package com.hkts.naturalgas.utils;

import com.hkts.naturalgas.dictionaries.entity.HktsEnvironmentProtectionItem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.prefs.BackingStoreException;

/**
 * @ClassNameExcelPortUtil
 * @Description
 * @Author yck
 * @Date2021-06-09 16:00
 * @Version V1.0
 **/
public class ExcelPortUtil {

    public static XSSFWorkbook getSheet(XSSFWorkbook wb,List<HktsEnvironmentProtectionItem> hktsEnvironmentProtectionItems,List<Map<String, Object>> list,String sheetName) throws ParseException {
        XSSFSheet sheet = wb.createSheet(sheetName);
        SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        sheet.setDefaultColumnWidth(19);
       // XSSFRow rowReportTitle = sheet.createRow(0);
/*        Cell cell1 = rowReportTitle.createCell(0); // 0列
        // 设置值
        cell1.setCellValue(sheetName);
        // 合并表头
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, hktsEnvironmentProtectionItems.size() - 2));
        rowReportTitle.setHeight((short) 600); // 行高*/

        //设置表头字体
      /*  Font headFont = wb.createFont();
        headFont.setFontName("宋体");
        headFont.setFontHeightInPoints((short) 18);// 字体大小

        CellStyle headStyle = wb.createCellStyle();
        headStyle.setFont(headFont);
        headStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        // 头部样式添加
        cell1.setCellStyle(headStyle);*/

        // 全局加线样式
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        // 标题写入
        XSSFRow row = sheet.createRow(0);

        // 记录标题信息
        TreeMap<String, Integer> headMap = new TreeMap<>();
        for (int i = 0; i < hktsEnvironmentProtectionItems.size(); i++) {
            if(hktsEnvironmentProtectionItems.get(i).getFieldName().equals("id")){
                continue;
            }
            row.setHeight((short) 450);
            XSSFCell cell = row.createCell(getColNum(row.getLastCellNum()));
            String headName = hktsEnvironmentProtectionItems.get(i).getFiledDescribe();
            cell.setCellValue(headName); // 写入列名
            headMap.put(headName, i);
            cell.setCellStyle(cellStyle);
        }

        // 写入内容数据
        int ind = 1;
        for (Map<String, Object> map : list) {
            XSSFRow r = sheet.createRow(ind++);
            for(int i=0;i<hktsEnvironmentProtectionItems.size();i++){

                if(hktsEnvironmentProtectionItems.get(i).getFieldName().equals("id")){
                    continue;
                }
                else if(hktsEnvironmentProtectionItems.get(i).getFieldName().equals("time")){
                    XSSFCell cell = r.createCell( getColNum(r.getLastCellNum()));
                    cell.setCellValue(format.format((Date) map.get(hktsEnvironmentProtectionItems.get(i).getFieldName())));
                }else{
                    if(map.get(hktsEnvironmentProtectionItems.get(i).getFieldName())!=null){
                        XSSFCell cell = r.createCell(getColNum(r.getLastCellNum()));
                        cell.setCellValue(map.get(hktsEnvironmentProtectionItems.get(i).getFieldName()).toString() );
                    }
                }
            }
        }
        return wb;
    }
    public static void excelPort(String sheetName, List<String> listName,List<String> listIndex ,List<Map<String, Object>> list, List<Map<Integer, String>> listBottom, HttpServletResponse response) {
        SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            if (list.size() == 0) {
                throw new BackingStoreException("数据为空");
            }
            // 声明一个工作簿
            XSSFWorkbook wb = new XSSFWorkbook();
            // 创建sheet页
            XSSFSheet sheet = wb.createSheet(sheetName);
            sheet.setDefaultColumnWidth(19);

            // 表头
            XSSFRow rowReportTitle = sheet.createRow(0);
            Cell cell1 = rowReportTitle.createCell(0); // 0列
            // 设置值
            cell1.setCellValue(sheetName);

            // 合并表头
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, listName.size() - 1));
            rowReportTitle.setHeight((short) 600); // 行高

            //设置表头字体
            Font headFont = wb.createFont();
            headFont.setFontName("宋体");
            headFont.setFontHeightInPoints((short) 18);// 字体大小

            CellStyle headStyle = wb.createCellStyle();
            headStyle.setFont(headFont);
            headStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            headStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            // 头部样式添加
            cell1.setCellStyle(headStyle);

            // 全局加线样式
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
            cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
            cellStyle.setBorderTop(BorderStyle.THIN);//上边框
            cellStyle.setBorderRight(BorderStyle.THIN);//右边框
            cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中

            // 记录标题信息
            TreeMap<String, Integer> headMap = new TreeMap<>();

            // 标题写入
            XSSFRow row = sheet.createRow(1);
            for (int i = 0; i < listName.size(); i++) {
                row.setHeight((short) 450);
                XSSFCell cell = row.createCell(i);
                String headName = listName.get(i);
                cell.setCellValue(headName); // 写入列名
                headMap.put(headName, i);
                cell.setCellStyle(cellStyle);
            }

            // 写入内容数据
            int ind = 2;
            for (Map<String, Object> map : list) {
                XSSFRow r = sheet.createRow(ind++);
               for(int i=0;i<listIndex.size();i++){
                   XSSFCell cell = r.createCell(i);
                   if(listIndex.get(i).equals("time")){
                       System.out.println(map.get(listIndex.get(i)).toString());
                       Date date = format.parse(map.get(listIndex.get(i)).toString());
                       cell.setCellValue(date);
                   }else{
                       if(map.get(listIndex.get(i))!=null){
                           cell.setCellValue(Float.parseFloat(map.get(listIndex.get(i)).toString()) );
                       }
                   }
               }
            }

            // 底部样式
            CellStyle bottomStyle = wb.createCellStyle();
            bottomStyle.setBorderBottom(BorderStyle.THIN); //下边框
            bottomStyle.setBorderLeft(BorderStyle.THIN);//左边框
            bottomStyle.setBorderTop(BorderStyle.THIN);//上边框
            bottomStyle.setBorderRight(BorderStyle.THIN);//右边框
            bottomStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中

            // 写入底部
            if (listBottom != null) {
                int columnNum = listName.size(); // 当前表有多少列
                for (Map<Integer, String> map : listBottom) {
                    XSSFRow bottom = sheet.createRow(ind);
                    bottom.setHeight((short) 400); // 行高
                    // 当前行分几列
                    int size = map.size();
                    if (columnNum % 2 == 0 & size % 2 == 0) {
                        // 都是偶数执行
                        int c = columnNum / size;  // 列大小
                        for (int i = 0; i < size; i++) {
                            CellRangeAddress cellAddresses0 = new CellRangeAddress(ind, ind, i * c, i * c + c - 1);
                            sheet.addMergedRegion(cellAddresses0);
                            XSSFCell c0 = bottom.createCell(cellAddresses0.getFirstColumn());
                            c0.setCellValue(map.get(i));
                            c0.setCellStyle(bottomStyle);
                            for (int n = cellAddresses0.getFirstColumn() + 1; n <= cellAddresses0.getLastColumn(); n++) {
                                XSSFCell cn = bottom.createCell(n);
                                cn.setCellStyle(bottomStyle);
                            }
                        }
                    } else if (!(columnNum % 2 == 0) & !(size % 2 == 0)) {
                        // 都是奇数执行
                        int c = columnNum - 1 / size;
                        for (int i = 0; i < size; i++) {
                            CellRangeAddress cellAddresses1;
                            if (size - i <= 1) {
                                cellAddresses1 = new CellRangeAddress(ind, ind, i * c, i * c + c);
                            } else {
                                cellAddresses1 = new CellRangeAddress(ind, ind, i * c, i * c + c - 1);
                            }
                            sheet.addMergedRegion(cellAddresses1);
                            XSSFCell c0 = bottom.createCell(cellAddresses1.getFirstColumn());
                            c0.setCellValue(map.get(i));
                            c0.setCellStyle(bottomStyle);
                            for (int n = cellAddresses1.getFirstColumn() + 1; n <= cellAddresses1.getLastColumn(); n++) {
                                XSSFCell cn = bottom.createCell(n);
                                cn.setCellStyle(bottomStyle);
                            }
                        }
                    } else {
                        // 奇偶不同
                        int c = (columnNum + 1) / size;
                        for (int i = 0; i < size; i++) {
                            CellRangeAddress cellAddresses2;
                            if (size - i <= 1) {
                                cellAddresses2 = new CellRangeAddress(ind, ind, i * c, i * c + c - 2);
                            } else {
                                cellAddresses2 = new CellRangeAddress(ind, ind, i * c, i * c + c - 1);
                            }
                            sheet.addMergedRegion(cellAddresses2);
                            XSSFCell c0 = bottom.createCell(cellAddresses2.getFirstColumn());
                            c0.setCellValue(map.get(i));
                            c0.setCellStyle(bottomStyle);
                            for (int n = cellAddresses2.getFirstColumn() + 1; n <= cellAddresses2.getLastColumn(); n++) {
                                XSSFCell cn = bottom.createCell(n);
                                cn.setCellStyle(bottomStyle);
                            }
                        }
                    }
                    ind++;
                }
            }

            // 输出Excel文件
            OutputStream output = response.getOutputStream();
            response.reset();
            // 设置文件头
            response.setHeader("Content-Disposition",
                    "attchement;filename=" + new String((sheetName + ".xls").getBytes("gb2312"), "ISO8859-1"));
            response.setContentType("application/msexcel");
            wb.write(output);
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void excelPortTime( HttpServletResponse response) {
        SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
       // SimpleDateFormat format1=new SimpleDateFormat("HH:mm:ss");
        try {

            // 声明一个工作簿
            XSSFWorkbook wb = new XSSFWorkbook();
            // 创建sheet页
            XSSFSheet sheet = wb.createSheet("时间表格");
            sheet.setDefaultColumnWidth(19);
            String dateStr="2021-7-9 15:00:00";

            String endStr="2021-7-20 13:00:00";
            Date parse = format.parse(dateStr);
            Date now=format.parse(endStr);
            // 表头
            XSSFRow rowReportTitle = sheet.createRow(0);
            Cell cell1 = rowReportTitle.createCell(0); // 0列
            int index=1;
            cell1.setCellValue("时间");
            while(parse.getTime()<now.getTime()){
                XSSFRow rowReportContent = sheet.createRow(index);
                Calendar c = new GregorianCalendar();
                c.setTime(parse);
                c.add(Calendar.HOUR,1);
                Date time = c.getTime();
                parse=time;
                Cell cellrow = rowReportContent.createCell(0);
                cellrow.setCellValue(format.format(time));
                index++;
            }

            // 输出Excel文件
            OutputStream output = response.getOutputStream();
            response.reset();
            // 设置文件头
            response.setHeader("Content-Disposition",
                    "attchement;filename=" + new String(("时间表格" + ".xls").getBytes("gb2312"), "ISO8859-1"));
            response.setContentType("application/msexcel");
            wb.write(output);
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /** 功能描述:
    *  获取列数
    * @return:
    * @Author: 杨春科
    * @Date: 2021-06-24 10:09
    * @Description //TODO
    */
    public static int getColNum(int num){
        if(num==-1){
            return 0;
        }else {
            return num;
        }
    }
}
